import pymysql


class MySQL:
    def __init__(self, host, port, user, password, database):
        """
        初始化数据库连接参数
        :param host: 数据库主机地址
        :param port: 数据库端口
        :param user: 数据库用户名
        :param password: 数据库密码
        :param database: 数据库名
        """
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self):
        """
        建立数据库连接
        """
        try:
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
            self.cursor = self.connection.cursor()
            print("数据库连接成功")
        except pymysql.Error as e:
            print(f"数据库连接失败: {e}")

    def execute_query(self, sql, params=None):
        """
        执行查询语句
        :param sql: SQL 查询语句
        :param params: 查询参数
        :return: 查询结果
        """
        if not self.connection:
            self.connect()
        try:
            self.cursor.execute(sql, params)
            result = self.cursor.fetchall()
            return result
        except pymysql.Error as e:
            print(f"查询执行失败: {e}")
            return []

    def execute_update(self, sql, params=None):
        """
        执行更新语句（如 INSERT, UPDATE, DELETE）
        :param sql: SQL 更新语句
        :param params: 更新参数
        :return: 受影响的行数
        """
        if not self.connection:
            self.connect()
        try:
            self.cursor.execute(sql, params)
            self.connection.commit()
            return self.cursor.rowcount
        except pymysql.Error as e:
            self.connection.rollback()
            print(f"更新执行失败: {e}")
            return 0

    def close(self):
        """
        关闭数据库连接
        """
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
            print("数据库连接已关闭")


# if __name__ == "__main__":
#     # 示例使用
#     db = MySQL(host='localhost', port=3306, user='root', password='121300', database='fastapi')
#     # 查询示例
#     select_sql = "SELECT * FROM users"
#     result = db.execute_query(select_sql)
#     print(result)
#     # 更新示例
#     insert_sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
#     rows_affected = db.execute_update(insert_sql, ('John', 25))
#     print(f"受影响的行数: {rows_affected}")
#     db.close()
